package dao.impl;

import bean.Student;
import dao.StudentDao;
import util.DBUtil;
import util.StudentEnum;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class StudentDaoImpl extends DBUtil implements StudentDao {

    /**
     * 获得学生信息
     * @param stuname
     * @param stuno
     * @param usex
     * @param pageIndex
     * @param pageSize
     * @return
     */
    @Override
    public List<Student> getStudnets(String stuname, String stuno, int usex, int pageIndex, int pageSize) {
        List<Student> list = new ArrayList<>();
        List<java.io.Serializable> params = new ArrayList<>();

        try {
            //模糊查询，并用StringBuffer进行字符串的拼接
            StringBuffer sqlbuf = new StringBuffer(" select * from student where 1=1 and state!=4 ");

            if (stuname!=null&&stuname.length()>0){
                sqlbuf.append(" and stuname like ? ");
                params.add("%"+stuname+"%");
            }

            if (stuno!=null&&stuno.length()>0){
                //拼接学号模糊查询条件
                sqlbuf.append(" and stuno like ? ");
                params.add("%"+stuno+"%");
            }

            if (usex!=-1){
                //拼接性别查询条件
                sqlbuf.append(" and sex=? ");
                params.add(usex);
            }

            //分页
            sqlbuf.append(" limit ?,? ");
            //limt (pageIndex-1)*pageSize,pageSize;
            params.add((pageIndex-1)*pageSize);
            params.add(pageSize);

            resultSet = query(sqlbuf.toString(),params);

            while (resultSet.next()){
                //存储学生信息
                Student student = new Student();
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setStuName(resultSet.getString("stuname"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setEmail(resultSet.getString("email"));
                student.setRegistered(resultSet.getString("registered"));
                student.setAddress(resultSet.getString("address"));
                student.setProfession(resultSet.getString("profession"));
                student.setIdNumber(resultSet.getString("idnumber"));
                student.setPolitics(resultSet.getString("politics"));
                student.setRegDate(resultSet.getDate("regdate"));
                student.setState(resultSet.getInt("state"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setGid(resultSet.getInt("gid"));

                //存储一个学生对象到集合中
                list.add(student);

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }


        return list;
    }

    /**
     * 查询总页数
     * @param stuname
     * @param stuno
     * @param usex
     * @return
     */
    @Override
    public int total(String stuname, String stuno, int usex) {
        int total = 0;

        List<java.io.Serializable> params = new ArrayList<java.io.Serializable>();

        try {
            //模糊查询，并用StringBuffer进行字符串的拼接
            StringBuffer sqlbuf = new StringBuffer(" select count(*) from student where 1=1 and state!=4 ");

            if (stuname!=null&&stuname.length()>0){
                //拼接学生名称模糊查询条件
                sqlbuf.append(" and stuname like ? ");
                params.add("%"+stuname+"%");
            }

            if (stuno!=null&&stuno.length()>0){
                //拼接学号模糊查询条件
                sqlbuf.append(" and stuno like ? ");
                params.add("%"+stuno+"%");
            }

            if (usex!=-1){
                //拼接性别查询条件
                sqlbuf.append(" and sex=? ");
                params.add(usex);
            }

            resultSet = query(sqlbuf.toString(),params);

            while (resultSet.next()){

                //循环完获得最后的id值
                total = resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }

        return total;
    }


    /**
     * 添加学生数据
     * @param student
     * @return
     */
    @Override
    public int inserStu(Student student) {

        int i = 0;

        try {
            String sql = " insert into student values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
            List params = new ArrayList();

            params.add(student.getStuName());
            params.add(student.getStuNo());
            params.add(student.getSex());
            params.add(student.getPhone());
            params.add(student.getEmail());
            params.add(student.getRegistered());
            params.add(student.getAddress());
            params.add(student.getProfession());
            params.add(student.getIdNumber());
            params.add(student.getPolitics());
            params.add(new Date());
            params.add(StudentEnum.READING.type); //在读
            params.add(student.getIntroduction());
            params.add(student.getGid());

            i = update(sql,params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }


        return i;

    }


    /**
     * 通过学号查询学生信息
     * @param sid
     * @return
     */
    @Override
    public Student findById(int sid) {

        Student student = new Student();

        try {
            String sql = " select * from student where stuid=? ";
            List param = new ArrayList();
            param.add(sid);

            resultSet = query(sql,param);

            while (resultSet.next()){
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuName(resultSet.getString("stuname"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setProfession(resultSet.getString("profession"));
                student.setAddress(resultSet.getString("address"));
                student.setRegDate(resultSet.getDate("regdate"));
                student.setEmail(resultSet.getString("email"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setGid(resultSet.getInt("gid"));
                student.setRegistered(resultSet.getString("registered"));
                student.setIdNumber(resultSet.getString("idnumber"));
                student.setPolitics(resultSet.getString("politics"));

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }


        return student;
    }


    /**
     * 修改学生信息
     * @param student
     * @return
     */
    @Override
    public int updateStu(Student student) {

        int updateRows = 0;

        try {
            String sql=" update student set stuname=?,stuno=?,sex=?,phone=?,email=?,registered=?,address=?,profession=?,idnumber=?,politics=?,introduction=?,gid=? where stuid=? ";

            List param = new ArrayList();
            param.add(student.getStuName());
            param.add(student.getStuNo());
            param.add(student.getSex());
            param.add(student.getPhone());
            param.add(student.getEmail());
            param.add(student.getRegistered());
            param.add(student.getAddress());
            param.add(student.getProfession());
            param.add(student.getIdNumber());
            param.add(student.getPolitics());
            param.add(student.getIntroduction());
            param.add(student.getGid());
            param.add(student.getStuId());

            updateRows = update(sql,param);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }


        return updateRows;
    }


    /**
     * 逻辑假删除学生信息
     * @param sid
     * @return
     */
    @Override
    public int delStu(String sid) {

        int i = 0;

        try {
            String sql = " update student set state=? where stuid=? ";

            List param = new ArrayList();

            param.add(StudentEnum.DELETE.type);  //删除
            param.add(sid);

            i = update(sql,param);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return i;
    }
}
